
package model.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import model.dao.EnahoConexion;
import model.dao.PDAConexion;
import model.dao.PDAParserXML;
import model.domain.bd.BaseDatos;
import util.jdbc.ConexionSQLServer;

public class Enaho001Service {
    public void guardarFromPDA(String archivoXML) throws Exception {     
        
        //De XML a objetos
        BaseDatos bd = new BaseDatos();
        PDAParserXML.parsear(bd, archivoXML);
        
        //Del objetos a SQlite
        PDAService s = new PDAService();
        s.borrarTablas_SQLite(bd);
        s.crearTablas_SQLite(bd);
        s.insertarData_SQLite(bd);
        
        //De sqlite al sqlserver
        guardarModulo_Caratula(bd);
    }
    
    private void guardarModulo_Caratula(BaseDatos bd) throws Exception {

        Connection con = null;
        Statement st = null;        
        Connection econ = null;
        PreparedStatement est = null;
               
        try {
            con = PDAConexion.getConexion();
            st = con.createStatement();
            
            String sql = "Select A.Conglome, A.Anho,A.Mes,A.Periodo,A.Estrato,A.Dpto,A.Prov,A.Dist,A.Zona,A.Dominio,A.TipoSeleccion,A.Odei," +
            "A.Aerini,A.Aerfin,A.Muestra,B.Manzana,B.NroVivienda,B.TipoVivienda,B.CatVia,B.NombreVia,B.Puerta," +
            "B.Interior,B.Piso,B.Mz,B.Lote,B.Km,B.NroBlock,B.Codccpp,B.nomccpp,B.Telefono,B.EsReemplazo,B.NroReemplazo," +
            "B.Apertura,B.NroHogares,C.Nombre_Jefe,C.Apellido_Jefe,C.FecResFin,C.ResFin,C.CodEnc," +
            "C.CodSupLoc, C.CodCoord, C.CodSupNac, C.Hogarentre, C.p22, C.p23 " +
            "From Conglomerado A inner join Vivienda B on (A.Conglome = B.Conglome) inner join Hogar C on (B.Conglome = C.Conglome) and (B.Vivienda=C.Vivienda) ";
            //"Where B.Conglome='" & Con & "' And B.Vivienda='" & Viv & "' And C.Hogar='" & Hog & "'"
            
            ResultSet rs = st.executeQuery(sql);
        
            String Con, Viv, Hog;
            String REEMP, VIVREEMP, UBIGEO01, CCPP01, T5ZONA, T5MZA;
            String T5AER, T5AERF, T5VIVI, T5CVIA, T5CNOM, T5NVIA, T5PUERTA, T5INTERIOR, T5PISO, T5VIAMZ;
            String T5LOTE, T5KM, T5TLFNO, T5JEFRZO, T5RESFEC, T5RESENC, T5ENCUES;
            String T5SUPLOC, T5COORDI, T5SUPNAC, T5PANEL, T5CAP200, T5CAP300, T5CAP400, T5CAP500, T5PROAGR, T5INGIND;
            String T5ENA02, T5ENA04, T5ENA1B, T5MZA_N, T5MZA_A, T5TLFN;
            String Manzana, LatitudGPS, LongitudGPS, LatOrigenGPS, LonOrigenGPS, DistanciaGPS, p22, p23;
            Integer NroVivienda, nPos;

            Con = rs.getString("Conglome");
            //Viv = rs.getString("Vivienda");
            //Hog = rs.getString("Hogar");
            //T5AER = rs.getString("Aerini");
            /*
            T5AERF = Formato(dr!Aerfin.ToString, 3, Zero)
            T5VIVI = Formato(dr!NroVivienda, 2, Blank)
            T5CVIA = Formato(dr!CatVia.ToString, 1, Blank)
            T5CNOM = FormatoR(F_Via(T5CVIA), 9, Blank)
            T5NVIA = FormatoR(dr!NombreVia.ToString, 30, Blank)
            T5PUERTA = FormatoR(dr!Puerta.ToString, 5, Blank)
            T5INTERIOR = FormatoR(dr!Interior.ToString, 3, Blank)
            T5PISO = FormatoR(dr!piso.ToString, 2, Blank)
            T5VIAMZ = FormatoR(dr!Mz.ToString, 3, Blank)
            T5LOTE = FormatoR(dr!Lote.ToString, 3, Blank)
            T5KM = FormatoR(dr!Km.ToString, 4, Blank)
            T5TLFNO = Space(8)
            T5JEFRZO = FormatoR(dr!nombre_jefe.ToString.Trim & Space(1) & dr!Apellido_Jefe.ToString.Trim, 40, Blank)
            T5RESFEC = dr!FecResFin.ToString
            T5RESFEC = Subs(T5RESFEC, 7, 4) & Subs(T5RESFEC, 4, 2) & Subs(T5RESFEC, 1, 2)
            T5RESENC = Formato(dr!ResFin.ToString, 1, Blank)
            Manzana = dr!Manzana.ToString
            T5TLFN = FormatoR(dr!Telefono.Trim, 9, Blank)
            p22 = dr!p22.ToString.Trim
            p23 = dr!p23.ToString.Trim
            */

            /*
            Cadena = Tipo_Reg & Con & Viv & Hog & NroHogares & REEMP & VIVREEMP & UBIGEO01 & CCPP01 & T5ZONA & T5MZA & T5MZA_N & _
                T5MZA_A & T5AER & T5AERF & T5VIVI & T5CVIA & T5CNOM & T5NVIA & T5PUERTA & T5INTERIOR & T5PISO & T5VIAMZ & T5LOTE & _
                T5KM & T5TLFN & T5JEFRZO & T5RESFEC & T5RESENC & T5ENCUES & T5SUPLOC & T5COORDI & T5SUPNAC & T5PANEL & T5CAP200 & _
                T5CAP300 & T5CAP400 & T5CAP500 & T5PROAGR & T5INGIND & T5ENA02 & T5ENA04 & T5ENA1B & LatitudGPS & LongitudGPS & _
                LatOrigenGPS & LonOrigenGPS & DistanciaGPS & p22 & p23
            */

            econ = EnahoConexion.getConexion();
            String sqlInsert = "insert into ENAHO001_CAP000(CONGLO01,VIVIEN01,NUMHOG01,TOTHOG01) values (?)";
            est = econ.prepareStatement(sqlInsert);
            while(rs.next()) {
                est.setString(1,Con);
                est.executeUpdate();
            }
        
        }         
        finally {
            if(est!=null) est.close();
            if(econ!=null) econ.close();

            if(st!=null) st.close();
            if(con!=null) con.close();
        }
    }

  
}
